package com.mingrisoft.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class ProductDao {
	
	//获取数据库连接
	public Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";
			String username= "root";
			String password = "1234";
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace();
		}catch(SQLException se){
			se.printStackTrace();
		}
		return conn;
	}
	
	//分页查询所有商品
	public List<ProductEntity> find(int page){
		List<ProductEntity> list = new ArrayList<ProductEntity>();
		Connection conn = getConnection();
		String sql = "select * from tb_product order by id desc limit ?,?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1)*ProductEntity.PAGE_SIZE);
			ps.setInt(2, ProductEntity.PAGE_SIZE);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				ProductEntity product = new ProductEntity();
				product.setId(rs.getInt("id"));
				product.setProductName(rs.getString("product_name"));
				product.setProductNum(rs.getInt("product_num"));
				product.setProductPrice(rs.getDouble("product_price"));
				product.setProductUnit(rs.getString("product_unit"));
				list.add(product);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException se) {
			// TODO: handle exception
			se.printStackTrace();
		}
		return list;
	}
	
	//查询总记录数
	public int findCount(){
		int count = 0;
		Connection conn = getConnection();
		String sql = "select count(*) from tb_product";
		try {
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return count;
	}

	public ProductDao() {
		// TODO Auto-generated constructor stub
	}

}
